package notRelevantAnymore;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;




import db.DB_SQL;
import db.DB_Tools;
import parsing.Category;
import parsing.City;
import parsing.GeoParser;
import parsing.PeopleParser;
import parsing.Person;

public class CreatePeopleCategoryUserGropTables {

	public static void main(String[] args) {

		/* create categories */
		/*
		 * Category sportPlayers= new Category("SportsMen");
		 * Category.categories.add(sportPlayers); Category actors= new
		 * Category("Actors"); Category.categories.add(actors); Category
		 * singers=new Category("Singers"); Category.categories.add(singers);
		 * /*insert categories to table
		 */
		DB_SQL mySQL = new DB_SQL();
		mySQL.connect();
		try {
			mySQL.getConn().setAutoCommit(false);
		} catch (SQLException e) {
			// TODO Auto generated catch block
			e.printStackTrace();
		}
		/*
		 * if (createCategoriesTable(mySQL)==0){
		 * System.out.println("Query Failed"); return; }
		 * 
		 * /*persons
		 */
		parsing.PeopleParser.parsePersonCategory(args[0]);
		parsing.PeopleParser.parsePersonAttr(args[0]);
//		if (createPeopleTable(mySQL) == 0) {
	//		System.out.println("Query Failed");
	//		return;
	//	}
		if (createPeopleCategoriesTable( mySQL)==0){
			System.out.println("Query Failed");
			return;
		}
		

	}

	/* category */
	public static int createCategoriesTable(DB_SQL mySQL) {
		Statement create = DB_Tools.String2Statements(
				statements.createTables.createCategoriesTable, mySQL.getStmt());
		if (mySQL.execStatment(create) == 0) {
			System.out.println("Query Failed");
		} else {
			System.out.println("Query Succeeded");
		}
		try {
			PreparedStatement pstm = mySQL.getConn().prepareStatement(
					statements.createTables.updateCategoriesTable);
			Iterator<Category> categoriesIter = Category.categories.iterator();
			while (categoriesIter.hasNext()) {
				if (addNewCategory(categoriesIter.next(), pstm) == 0) {
					System.out.println("Query Failed");
					return 0;
				}
				mySQL.getConn().commit();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return 0;
		}
		return 1;

	}

	/*
	 * add category to the category table including executebatch
	 */
	public static int addNewCategory(Category newCategory,
			PreparedStatement pstm) {
		try {
			pstm.setString(1, newCategory.getCategoryName());
			pstm.addBatch();
			pstm.executeBatch();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return 0;
		}
		return 1;
	}

	/* people table */

	public static int createPeopleTable(DB_SQL mySQL) {
		int i = 0;
		Statement drop = DB_Tools.String2Statements(
				statements.createTables.dropPeopleTable, mySQL.getStmt());
		if (mySQL.execStatment(drop) == 0) {
			System.out.println("Query Failed");
		} else {
			System.out.println("Query Succeeded");
		}
		Statement create = DB_Tools.String2Statements(
				statements.createTables.createPeopleTable, mySQL.getStmt());
		if (mySQL.execStatment(create) == 0) {
			System.out.println("Query Failed");
		} else {
			System.out.println("Query Succeeded");
		}
		try {
			PreparedStatement pstm = mySQL.getConn().prepareStatement(
					statements.createTables.updatePeopleTable);
			for (Map.Entry<String, Person> entry : PeopleParser.persons
					.entrySet()) {
				if ((entry.getValue().getBirthCity() != null)
						|| (entry.getValue().getDeathCity() != null)) {
					if (addNewPerson(entry.getValue(), pstm) == 0) {
						System.out.println("Query Failed");
					}
					i++;
					if (i == 100000) {
						pstm.executeBatch();
						mySQL.getConn().commit();
						i = 0;
					}
				} else {
					continue;
				}
			}
			pstm.executeBatch();
			mySQL.getConn().commit();
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("Query Failed");
			return 0;
		}
		return 1;
	}

	public static int addNewPerson(Person newPerson, PreparedStatement pstm) {

		try {
			pstm.setLong(1, newPerson.getId());
			pstm.setString(2, newPerson.getName());
			pstm.setString(3, newPerson.getBirthCity());
			pstm.setString(4, newPerson.getDeathCity());
			pstm.addBatch();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return 0;
		}

		return 1;
	}

	/**************************** peopleCategories ******************************/
	public static int createPeopleCategoriesTable(DB_SQL mySQL) {
		List<Integer> categories;
		Statement drop=DB_Tools.String2Statements(statements.createTables.dropPeopleCategoriesTable,
				mySQL.getStmt());
		if (mySQL.execStatment(drop) == 0) {
			System.out.println("Query Failed");
		} else {
			System.out.println("Query Succeeded");
		}
		Statement create = DB_Tools.String2Statements(
				statements.createTables.createPeopleCategoriesTable,
				mySQL.getStmt());
		if (mySQL.execStatment(create) == 0) {
			System.out.println("Query Failed");
		} else {
			System.out.println("Query Succeeded");
		}
		try {
			PreparedStatement pstm = mySQL.getConn().prepareStatement(
					statements.createTables.updatePeopleCategoriesTable);
			for (Map.Entry<String, Person> entry : PeopleParser.persons
					.entrySet()) {
				if ((entry.getValue().getBirthCity() != null)
						|| (entry.getValue().getDeathCity() != null)) {
					categories = entry.getValue().getCategories();
					Iterator<Integer> categoriesIter = categories.iterator();
					while (categoriesIter.hasNext()) {
						if (addNewPersonPersonCategory(
								entry.getValue().getId(),
								categoriesIter.next(), pstm) == 0) {
							System.out.println("Query Failed");
						}
					}
					pstm.executeBatch();
					mySQL.getConn().commit();
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("Query Failed");
			return 0;
		}
		return 1;
	}

	private static int addNewPersonPersonCategory(int personId, Integer categoryId,
			PreparedStatement pstm) {
		try {
			pstm.setLong(1, personId);
			pstm.setLong(2, categoryId);
			pstm.addBatch();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return 0;
		}

		return 1;
	}
	
	/*******************city*******************************************************/
	// ###############city#####################################
		/*creating and updating city table*/
		public static void createCitiesTable(DB_SQL mySQL, PreparedStatement pstm) {
			int i = 0;
			Statement drop = DB_Tools.String2Statements(
					statements.createTables.dropCitiesTable, mySQL.getStmt());
			if (mySQL.execStatment(drop) == 0) {
				System.out.println("Query Failed");
			} else {
				System.out.println("Query Succeeded");
			}
			Statement create = DB_Tools.String2Statements(
					statements.createTables.createCitiesTable, mySQL.getStmt());
			if (mySQL.execStatment(create) == 0) {
				System.out.println("Query Failed");
			} else {
				System.out.println("Query Succeeded");
			}
			for (Map.Entry<String, City> entry : GeoParser.cities.entrySet()) {
				if (addNewCity(entry.getValue(), pstm) == 0) {
					System.out.println("Query Failed");
				}
				i++;
				if (i == 100000) {
					try {
						pstm.executeBatch();
						mySQL.getConn().commit();
					} catch (SQLException e) {
						e.printStackTrace();
						System.out.println("Query Failed");
					}
					i=0;
				}
				// GeoParser.cities.remove(entry.getKey());
			}
			try {
				pstm.executeBatch();
				mySQL.getConn().commit();
			} catch (SQLException e) {
				e.printStackTrace();
				System.out.println("Query Failed");
			}
		}

		/*add new city*/
		public static int addNewCity(City newCity, PreparedStatement pstm) {
			try {
				pstm.setLong(1, newCity.getCountryId());
				pstm.setString(2, newCity.getCityName());
				pstm.setFloat(3, newCity.getLatitude());
				pstm.setFloat(4, newCity.getLongtitude());
				pstm.addBatch();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return 0;
			}

			return 1;
		}
		
		//part of main of createGeoTables_temp
		/* updating cities */
		/*GeoParser.parserContinentCountryCity(args[1], 0);
			try {
			PreparedStatement pstm = mySQL.getConn().prepareStatement(
					statements.createTables.updateCitiesTable);
			createCitiesTable(mySQL, pstm);
			mySQL.getConn().commit();
			pstm.close();
			// mySQL.getConn().commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
*/
		
		}
